﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Maticsoft.DBUtility;
using MySql.Data.MySqlClient;
using EnvironmentManagerSystem.MODE;

namespace EnvironmentManagerSystem.BLL
{
    public class ProjectLeader
    {
        /// <summary>
        /// 获取项目负责人列表
        /// </summary>
        /// <param name="EvaluationUnitID">评价单位ID</param>
        /// <returns></returns>
        public static ProjectLeaderDataCollection GetList(int UnitID)
        {
            try
            {
                ProjectLeaderDataCollection list = new ProjectLeaderDataCollection();
                string strSql = string.Format(@"SELECT A.* FROM `TBS001_PROJECTLEADER` A,`TBS001_EVALUATIONUNIT` B WHERE A.UNITID=B.ID AND A.UNITID={0}", UnitID);
                using (MySqlDataReader dr = DbHelperMySQL.ExecuteReader(strSql))
                {
                    while (dr.Read())
                    {
                        ProjectLeaderData model = new ProjectLeaderData();
                        model = BindDataReader(dr);

                        list.Add(model);
                    }
                }
                return list;
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 获取项目负责人
        /// </summary>
        /// <returns></returns>
        public static ProjectLeaderData GetModel()
        {
            try
            {
                string strSql = string.Format(@"SELECT * FROM `TBS001_PROJECTLEADER` ORDER BY ID DESC LIMIT 1");
                ProjectLeaderData model = new ProjectLeaderData();
                using (MySqlDataReader dr = DbHelperMySQL.ExecuteReader(strSql))
                {
                    while (dr.Read())
                    {
                        model = BindDataReader(dr);
                    }
                }
                return model;
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 获取项目负责人
        /// </summary>
        /// <returns></returns>
        public static ProjectLeaderData GetModel(int ID)
        {
            try
            {
                string strSql = string.Format(@"SELECT * FROM `TBS001_PROJECTLEADER` WHERE ID={0}", ID);
                ProjectLeaderData model = new ProjectLeaderData();
                using (MySqlDataReader dr = DbHelperMySQL.ExecuteReader(strSql))
                {
                    while (dr.Read())
                    {
                        model = BindDataReader(dr);
                    }
                }
                return model;
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 获取项目负责人
        /// </summary>
        /// <returns></returns>
        public static ProjectLeaderDataCollection GetList(string IDList)
        {
            try
            {
                string strSql = string.Format(@"SELECT * FROM `TBS001_PROJECTLEADER` WHERE ID in ({0})", IDList);
                ProjectLeaderDataCollection list = new ProjectLeaderDataCollection();
                using (MySqlDataReader dr = DbHelperMySQL.ExecuteReader(strSql))
                {
                    while (dr.Read())
                    {
                        ProjectLeaderData model = new ProjectLeaderData();
                        model = BindDataReader(dr);
                        list.Add(model);
                    }
                }
                return list;
            }
            catch
            {
                return null;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static ProjectLeaderDataCollection GetLists(string IDList)
        {
            try
            {
                string strSql = string.Format(@"SELECT * FROM `TBS001_PROJECTLEADER` WHERE UnitID in ({0})", IDList);
                ProjectLeaderDataCollection list = new ProjectLeaderDataCollection();
                using (MySqlDataReader dr = DbHelperMySQL.ExecuteReader(strSql))
                {
                    while (dr.Read())
                    {
                        ProjectLeaderData model = new ProjectLeaderData();
                        model = BindDataReader(dr);
                        list.Add(model);
                    }
                }
                return list;
            }
            catch
            {
                return null;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public static bool AddProjectLeader(ProjectLeaderData model)
        {
            try
            {
                string strSql = string.Format("INSERT INTO `TBS001_PROJECTLEADER` (Name,UnitID,CredentialsNo,EIAType,CertificatePicURL,AppointmentCardPicURL) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}')", 
                                                          model.Name, model.UnitID, model.CredentialsNo, model.EIAType, model.CertificatePicURL, model.AppointmentCardPicURL);
                return DbHelperMySQL.ExecuteSql(strSql) > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public static bool UpdateProjectLeader(ProjectLeaderData model)
        {
            try
            {
                string strSql = string.Format("UPDATE `TBS001_PROJECTLEADER` set Name='{0}',UnitID='{1}',CredentialsNo='{2}',EIAType='{3}',CertificatePicURL='{4}',AppointmentCardPicURL='{5}' WHERE ID={6}"
                                                        , model.Name, model.UnitID, model.CredentialsNo, model.EIAType, model.CertificatePicURL, model.AppointmentCardPicURL, model.ID);
                return DbHelperMySQL.ExecuteSql(strSql) > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }

       /// <summary>
       /// 
       /// </summary>
       /// <param name="IDList"></param>
       /// <returns></returns>
        public static bool UpdateProjectLeader(int UnitID, string IDList)
        {
            try
            {
                string strSql = string.Format(@"UPDATE `TBS001_PROJECTLEADER` set UnitID='{0}' WHERE ID in ({1})", UnitID, IDList);
                return DbHelperMySQL.ExecuteSql(strSql) > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }
        /// <summary>
        /// 删除项目负责人
        /// </summary>
        /// <param name="ID">负责人编号</param>
        /// <returns></returns>
        public static bool Delete(int ID)
        {
            try
            {
                string strSql = string.Format(@"delete from `TBS001_PROJECTLEADER` where ID={0}", ID);
                return DbHelperMySQL.ExecuteSql(strSql) > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }


        /// <summary>
        /// 根据单位ID删除项目负责人
        /// </summary>
        /// <param name="ID">评价单位编号</param>
        /// <returns></returns>
        public static bool DeleteByUnitID(int UnitID)
        {
            try
            {
                string strSql = string.Format(@"delete from `TBS001_PROJECTLEADER` where UnitID={0}", UnitID);
                return DbHelperMySQL.ExecuteSql(strSql) > 0 ? true : false;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 证件号是否存在
        /// </summary>
        /// <param name="use"></param>
        /// <returns></returns>
        public static bool WhetherExistence(string CredentialsNo)
        {
            DataTable dt = DbHelperMySQL.Query(string.Format("SELECT ID FROM tbs001_projectleader WHERE CredentialsNo='{0}'", CredentialsNo)).Tables[0];
            if (dt.Rows.Count > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 证件号是否存在
        /// </summary>
        /// <param name="use"></param>
        /// <returns></returns>
        public static bool WhetherExistence(string CredentialsNo, int projectleaderID)
        {
            DataTable dt = DbHelperMySQL.Query(string.Format("SELECT ID FROM tbs001_projectleader WHERE CredentialsNo='{0}' and ID!={1}", CredentialsNo, projectleaderID)).Tables[0];
            if (dt.Rows.Count > 0)
                return true;
            else
                return false;
        }

        public static ProjectLeaderData BindDataReader(MySqlDataReader dr)
        {
            ProjectLeaderData model = new ProjectLeaderData();
            model.ID = (int)dr["ID"];
            model.UnitID = (int)dr["UnitID"];
            model.Name = dr["Name"].ToString();
            model.CredentialsNo = dr["CredentialsNo"].ToString();
            //model.CertificatePic = Encoding.Default.GetBytes(dr["CertificatePic"].ToString());
            model.EIAType = dr["EIAType"].ToString();
            //model.AppointmentCardPic = Encoding.Default.GetBytes(dr["AppointmentCardPic"].ToString());
            model.CertificatePicURL = dr["CertificatePicURL"].ToString();
            model.AppointmentCardPicURL = dr["AppointmentCardPicURL"].ToString();
            return model;
        }
    }
}
